package BOH.data;

import java.sql.*;
import java.util.*;
import java.util.logging.*;
import javax.swing.*;
//import org.apache.derby.jdbc.ClientDriver;

/**
 * The Back of House System for a
 * small restaurant.
 * 
 * Maeghan Colvin & Jennifer Havemman
 * CSC 289 0B01
 * February 13, 2014
 */

public class EateryDBConnection 
{
    /**
     *
     * @return
     */
    public static ResultSet getTableResults(String sqlStatement)
    {
        try
        {
            // Connect to database
            String url = "jdbc:derby:EateryDB";
            ResultSet results;
            try (Connection conn = DriverManager.getConnection(url))
            {
                Statement stmt = conn.createStatement(
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
                results = stmt.executeQuery(sqlStatement);
                conn.close();
            }

            return results;
        }
        catch (SQLException ex)
        {
            Logger.getLogger(EateryDBConnection.class.getName()).
                    log(Level.SEVERE,
                    null,
                    ex);
            return null;
        }
    }

    /**
     * The getSearch method returns a 
     * boolean search on the database.
     * @return
     */
    public static boolean getSearch(String sqlStatement)
    {
        boolean found = false;

        try
        {
            // Connect to database
            String url = "jdbc:derby:EateryDB";
            try (Connection conn = DriverManager.getConnection(url))
            {
                Statement stmt = conn.createStatement(
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);

                // Get ResultSet
                ResultSet results = stmt.executeQuery(sqlStatement);

                // Search ResultSet to find if data was found
                if (results.next())
                {
                    found = true;
                }
                else
                {
                    found = false;
                }
                conn.close();
            }

            return found;
        }
        catch (SQLException ex)
        {
            Logger.getLogger(EateryDBConnection.class.getName()).
                    log(Level.SEVERE,
                    null,
                    ex);
            return found;
        }
    }
    
    /**
     * The getDate method returns an 
     * array of information from the database.
     * @return
     */
    public static String[] getData(String sqlStatement)
    {
        String[] resultsArray;

        try
        {
            // Connect to database
            String url = "jdbc:derby:EateryDB";
            try (Connection conn = DriverManager.getConnection(url))
            {
                Statement stmt = conn.createStatement(
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);

                // Get ResultSet
                ResultSet results = stmt.executeQuery(sqlStatement);

                // Search ResultSet to find if data was found
                ArrayList<String> rowValues = new ArrayList<>();
                while (results.next()) 
                {
                    int i = 1;
                    
                    ResultSetMetaData metadata = results.getMetaData();
                    int numberOfColumns = metadata.getColumnCount();
                    while(i <= numberOfColumns) 
                    {
                        rowValues.add(results.getString(i++).trim());
                    }
                }   

                conn.close();
                
                resultsArray = (String[]) rowValues.toArray(new String[rowValues.size()]);
            }
            
            return resultsArray;
        }
        catch (SQLException ex)
        {
            Logger.getLogger(EateryDBConnection.class.getName()).
                    log(Level.SEVERE,
                    null,
                    ex);
            return null;
        }
    }
    
    /**
     * The getArrayListData gets information from
     * database and returns an arrayList.
     * @param sqlStatement
     * @return 
     */
    public static ArrayList<String> getArrayListData(String sqlStatement)
    {
        ArrayList<String> rowValues;
        try
        {
            // Connect to database
            String url = "jdbc:derby:EateryDB";
            try (Connection conn = DriverManager.getConnection(url))
            {
                Statement stmt = conn.createStatement(
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);

                // Get ResultSet
                ResultSet results = stmt.executeQuery(sqlStatement);

                // Search ResultSet to find if data was found
                rowValues = new ArrayList<>();
                while (results.next()) 
                {
                    int i = 1;
                    
                    ResultSetMetaData metadata = results.getMetaData();
                    int numberOfColumns = metadata.getColumnCount();
                    while(i <= numberOfColumns) 
                    {
                        rowValues.add(results.getString(i++).trim());
                    }
                }   

                conn.close();
            }
            
            return rowValues;
        }
        catch (SQLException ex)
        {
            Logger.getLogger(EateryDBConnection.class.getName()).
                    log(Level.SEVERE,
                    null,
                    ex);
            return null;
        }
    }

    /**
     * The getDataString method returns a 
     * String result from the database.
     * @return
     */
    public static String getDataString(String sqlStatement)
    {
        String getString = "";

        try
        {
            // Connect to database
            String url = "jdbc:derby:EateryDB";
            try (Connection conn = DriverManager.getConnection(url))
            {
                Statement stmt = conn.createStatement();

                // Get ResultSet
                ResultSet results = stmt.executeQuery(sqlStatement);
                
                while (results.next()) 
                {
                    getString = results.getString(1).trim();
                }
                
                conn.close();
                
                // Return
                return getString;
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(EateryDBConnection.class.getName()).
                    log(Level.SEVERE,
                    null,
                    ex);
            return null;
        }
    }
    
    /**
     * The updateDatavase method is used
     * to send update statements to the database.
     * @param sqlStatement 
     */
    public static void updateDatabase(String sqlStatement)
    {
        // Connect to database
        String url = "jdbc:derby:EateryDB";
        try (Connection conn = DriverManager.getConnection(url))
        {
            Statement stmt = conn.createStatement();
            stmt.execute(sqlStatement);
            conn.close();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(EateryDBConnection.class.getName()).
                    log(Level.SEVERE,
                    null,
                    ex);
            JOptionPane.showMessageDialog(null,
                    "ERROR IN SQL: " + ex.getMessage());
        }
    }
}
